import shapely
from shapely.geometry import MultiPolygon,Polygon,mapping,shape
import json


class Filter2SqlWhere():
   
    def __init__(self,filter) :
        self.filter=filter
        self.joinjudge=False
    def query_to_sql(self):
        """cql转换sql语句的where条件,改进后采用递归方法,实现无限嵌套查询的sql语句转换
           author:CCC
        """

        
        def parse_condition(cond):
            op = cond['op']
            args = cond['args']

            if op in ['=', '<', '>', '<=', '>=', 'like','!=','']:
                column = args[0]['property']
                if column=="dataset_id":
                    self.joinjudge=True
                value = args[1]
                if isinstance(value, str):
                    value = f"'{value}'"
                return f"{column} {op} {value}"
            elif op in ['and', 'or']:
                return f" {op.upper()} ".join([f"({parse_condition(arg)})" for arg in args])
            elif op == 'in':
                column = args[0]['property']
                if column=="dataset_id":
                    self.joinjudge=True
                values = ', '.join([f"'{v}'" if isinstance(v, str) else str(v) for v in args[1]])
                return f"{column} IN ({values})"
            elif op=='isnull':
                column = args[0]['property']
                return f"{column} is null"
            elif op=='between':
                column = args[0]['property']
                values =  args[1]
                return f"{column} {op.upper()} {values[0]} AMD {values[1]}"
            elif op=='s_intersects':
                column = args[0]['property']
                values = args[1]
               
                geo = shape(values)
    
                if type(geo) == MultiPolygon:
                    geos = shapely.unary_union(geo).convex_hull
                    geowkt = shapely.to_wkt(geos)
                    
                elif type(geo) == Polygon:
                    polygoncount=len(geo.exterior.xy[0])
                    if polygoncount>50:
                        geos = shapely.unary_union(geo).convex_hull
                        geowkt = shapely.to_wkt(geos)
                    else:
                        geowkt = shapely.to_wkt(geo)
                    
                result =  "ST_INTERSECTS({},ST_SIMPLIFYPRESERVETOPOLOGY(({}),0.01))".format(str(column),"'"+geowkt+"'")
                return result
            elif op=="s_contains":
                column = args[0]['property']
                values = args[1]
                geo = shape(values)
    
                if type(geo) == MultiPolygon:
                    geos = shapely.unary_union(geo).convex_hull
                    geowkt = shapely.to_wkt(geos)
                    
                elif type(geo) == Polygon:
                    polygoncount=len(geo.exterior.xy[0])
                    if polygoncount>50:
                        geos = shapely.unary_union(geo).convex_hull
                        geowkt = shapely.to_wkt(geos)
                    else:
                        geowkt = shapely.to_wkt(geo)
                result =  "ST_CONTAINS({},ST_SIMPLIFYPRESERVETOPOLOGY(({}),0.01))".format(str(column),"'"+geowkt+"'")
                return result
            elif op =="s_within":
                column = args[0]['property']
                values = args[1]
                geo = shape(values)
    
                if type(geo) == MultiPolygon:
                    geos = shapely.unary_union(geo).convex_hull
                    geowkt = shapely.to_wkt(geos)
                    
                elif type(geo) == Polygon:
                    polygoncount=len(geo.exterior.xy[0])
                    if polygoncount>50:
                        geos = shapely.unary_union(geo).convex_hull
                        geowkt = shapely.to_wkt(geos)
                    else:
                        geowkt = shapely.to_wkt(geo)
                        
                result =  "ST_WITHIN({},ST_SIMPLIFYPRESERVETOPOLOGY(({}),0.01))".format(str(column),"'"+geowkt+"'")
            elif op=="t_intersects":
                if args[1]["interval"][0]=="..":
                    result = str(args[0]["property"]) + " <= "+"'"+str(args[1]["interval"][1])+"'"
                elif args[1]["interval"][1]=="..":
                    result = str(args[0]["property"]) + " >= " + "'"+str(args[1]["interval"][0])+"'"
                else:
                    result = str(args[0]["property"]) + " >= " +"'"+ str(args[1]["interval"][0])+"'"+" and "+str(args[0]["property"]) + " <= " +"'"+ str(args[1]["interval"][1])+"'"
                return result
            else:
                raise ValueError(f"Unsupported operation: {op}")
        
        sql_condition = parse_condition(self.filter)

        return sql_condition,self.joinjudge